<?php
/**
 * @desc : 数据库操作基类, 保证同一个数据库只有一个连接实例存在
 * @author : Ivan<hanwei@leju.sina.com.cn>
 */
//require_once($_SERVER['DOCUMENT_ROOT'].'/class/base/exception.class.php');
class lib_base_db
{
    private $_r = null; //读库连接实例
    private $_w = null; //写库连接实例
    private $_config = array();
    private $_debug = FALSE;
    private $_character_set = 'utf8';

    public function __construct($config = array())
    {
        if (empty($config))
        {
            //读库参数
            $this->_config['r']['dsn'] = "mysql:host={$_SERVER['SINASRV_DB_HOST_R']};
                                         port={$_SERVER['SINASRV_DB_PORT_R']};
                                         dbname={$_SERVER['SINASRV_DB_NAME_R']}";
            $this->_config['r']['user'] = $_SERVER['SINASRV_DB_USER_R'];
            $this->_config['r']['pass'] = $_SERVER['SINASRV_DB_PASS_R'];
            //写库参数
            $this->_config['w']['dsn'] = "mysql:host={$_SERVER['SINASRV_DB_HOST']};
                                         port={$_SERVER['SINASRV_DB_PORT']};
                                         dbname={$_SERVER['SINASRV_DB_NAME']}";
            $this->_config['w']['user'] = $_SERVER['SINASRV_DB_USER'];
            $this->_config['w']['pass'] = $_SERVER['SINASRV_DB_PASS'];
        }
        else
        {
            if (!is_array($config) || !isset($config['r']['host']) || !isset($config['r']['port']) ||
             !isset($config['r']['name']) || !isset($config['r']['user']) || !isset($config['r']['pass']))
            {
                throw new lib_base_exception('read database params wrong');
            }
            //读库参数
            $this->_config['r']['dsn'] = "mysql:host={$config['r']['host']};
                                         port={$config['r']['port']};
                                         dbname={$config['r']['name']}";
            $this->_config['r']['user'] = $config['r']['user'];
            $this->_config['r']['pass'] = $config['r']['pass'];
            //写库参数
            if (empty($config['w']))
            { //如果没有配置写库参数，默认为读写使用一个库
                $config['w'] = $config['r'];
            }
            if (!isset($config['w']['host']) || !isset($config['w']['port']) ||
             !isset($config['w']['name']) || !isset($config['w']['user']) || !isset($config['w']['pass']))
            {
                throw new lib_base_exception('read database params wrong');
            }
            $this->_config['w']['dsn'] = "mysql:host={$config['w']['host']};
                                         port={$config['w']['port']};
                                         dbname={$config['w']['name']}";
            $this->_config['w']['user'] = $config['w']['user'];
            $this->_config['w']['pass'] = $config['w']['pass'];
            //设置字符集
            if (isset($config['encoding']))
            {
                $this->_character_set = $config['encoding'];
            }
        }
    }

    /**
     * 连接数据库
     *
     * @param String $type r|w
     * @return Object PDO
     */
    public function connect($type = 'r')
    {
        global $registry;
        require_once('registry.class.php');
        if (!isset($registry))
        {
            $registry = lib_base_registry::get_instance();
        }

        $conn = $registry->get($this->_config[$type]['dsn']);
        if (empty($conn))
        {
            $conn = new PDO($this->_config[$type]['dsn'],$this->_config[$type]['user'],$this->_config[$type]['pass']);
            //修改默认错误处理方式为抛出异常
            $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $conn->exec("SET NAMES {$this->_character_set}");
            $registry->set($this->_config[$type]['dsn'],$conn);
        }
        $instance = '_'.$type;
        return $this->$instance = $conn;
    }

    /**
     * 执行sql
     *
     * @param String $sql
     */
    public function exec($sql)
    {
        $type = ('select' == strtolower(substr(trim($sql), 0, 6))) ? 'r' : 'w';
        $instance = '_'.$type;
        if (! $this->$instance)
        {
            $this->connect($type);
        }
        if(!$this->ping($type))//mysql ping
        {
            throw new lib_base_exception('无可用mysql链接');
        }
        $sql = trim($sql);
        //记录不合理查询到库
        //if(preg_match("/^(SELECT)(.*)/is", $sql))
       // {
         //   $this->analyse_explian_info($sql, $this->$instance);
       // }
        $ret = $this->$instance->exec($sql);
        if (FALSE === $ret)
        {
            throw new lib_base_exception(print_r($this->$instance->errorInfo()));
        }
        return $ret;
    }

    /**
     * 数据库插入操作
     *
     * @param array $info
     * @return int
     */
    public function insert($table, $data)
    {
        $keys = '`' . implode('`,`', array_keys($data)) . '`';
        $values = "'" . implode('\',\'', array_values($data)) . "'";
        if (!isset($this->_w)){
            $this->connect('w');
        }

        if ($this->_debug)
        {
            echo $sql="INSERT INTO $table ($keys) VALUES ($values)";
        }
        $insert_res = $this->_w->exec("INSERT INTO `$table` ($keys) VALUES ($values)");
        $return_res = $this->_w->lastInsertId();
		return $return_res ;
	}

    /**
     * 更新数据库
     *
     * @param String $table
     * @param Array $data
     * @param String $condition
     * @return Intger 影响行数
     */
    public function update($table, $data, $condition)
    {
        $set_info = array();
        foreach ($data as $key => $value)
        {
            $set_info[] = '`' . $key . '` = ' . "'$value'";
        }
        $set_str = implode(',', $set_info);
        if (!isset($this->_w)){
            $this->connect('w');
        }
        if(!empty($condition))
        {
            $condit = ' WHERE '. $condition;
        }
        return $this->_w->exec("UPDATE `$table` SET $set_str $condit");
    }
    
    /**
     * 执行sql语句
     *
     */
    public function exec_sql($sql)
    {
		if (!isset($this->_w)){
			$this->connect('w');
        }
        
		return $this->_w->exec($sql);
	}

    /**
     * 替换已存在的记录
     *
     */
    public function replace()
    {

    }

    /**
     * 取符合条件的一条记录
     *
     * @param sql $sql
     * @return array
     */
    public function fetchOne($sql, $field)
    {
        if (!isset($this->_r))
        {
            $this->connect('r');
        }

        $record = $this->fetchRow($sql);
        return $record[$field];
    }

    /**
     * 取符合条件的一条记录
     *
     * @param sql $sql
     * @return array
     */
    public function fetchRow($sql)
    {
        if (!isset($this->_r))
        {
           $this->connect('r');
        }

        if(!$this->ping('r'))//mysql ping
        {
            throw new lib_base_exception('无可用mysql链接');
        }
        //$this->analyse_explian_info($sql, $this->_r);
        $stmt = $this->_r->query($sql);
        if ($stmt)
        {
            return $stmt->fetch(PDO::FETCH_ASSOC);
        }
        else
        {
            if ($this->_debug)
            {
                echo $sql;
                print_r($this->_r->errorInfo()) ;
            }
        }
        return FALSE;
    }

    /**
     * 取出所有满足条件的数据
     *
     * @param string $sql
     * @return array
     */
    public function fetchAll($sql)
    {
        if (!isset($this->_r))
        {
            $this->connect('r');
        }
                if(array_key_exists('debug', $_GET))
                {
                    if($_GET['debug']=='sinaleju')
                    {
                        echo $sql;
                    }
                }
                if(!$this->ping('r'))//mysql ping
                {
                    throw new lib_base_exception('无可用mysql链接');
                }
        //$this->analyse_explian_info($sql, $this->_r);
        $stmt = $this->_r->query($sql);
        if ($stmt)
        {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        else
        {
            if ($this->_debug)
            {
                echo $sql;
                print_r($this->_r->errorInfo()) ;
            }
        }
        return FALSE;
    }

    /**
     * 销毁某个数据库连接实例
     * @param String $type r|w
     */
    public function close($type='r')
    {
        $conn = '_'.$type;
        unset($this->$conn);
        //清除注册表内对象实例
        global $registry;
        if (!isset($registry))
        {
            $registry = lib_base_registry::get_instance();
        }
        $registry->clear($this->_config[$type]['dsn']);
    }

    /**
     *
     * pdo ping
     * 处理mysql has gone away问题,重新连接
     * @param $type
     * @return bool
     * */
    public function ping($type)
    {
        try
        {   //执行一个简单查询,看能否抛出异常
            $instance = '_'.$type;
            $ret = $this->$instance->prepare('select 1');
            $ret ->execute();
        }
        catch (Exception $e)
        {
            $err_info = $ret->errorInfo();
            if ($err_info[1] == '2006') //mysql has gone away
            {
                //分别把当前pdo对象置null，重新new pdo对象
                $this->close($type);
                $this->connect($type);
                return true;
            }
            else
            {   //其他异常,exit
                if($this->debug)
                {
                    echo 'message:',$e->getMessage(),'<br />';
                    print_r($this->smf->errorInfo());
                    exit();
                }
                else
                {
                    return false;
                }
            }
        }
        //无任何异常，说明当前连接可用
        return true;
    }

    /**
     * 分析explain结果并根据条件筛选记录  已经关闭不用2011-06-10
     * @param $sql string , $db resource, $table string, $condition array
     * @return void
     * author:Riven<jiangtao1@leju.sina.com.cn>
     * date:2011-06-09
     *
     */
    protected function analyse_explian_info($sql, $db, $table = "ws_explain_log", $condition = array())
    {
        if(!$condition)
        {
            $condition['type'] = "(all|range|indexhe)";
            $condition['Extra'] = '(temporary)';
            $condition['rows'] = "[0-9]{5,}";
        }
        $sql = trim($sql);
        $sql_reg = $sql;
        $sql = preg_replace("/(^[SELECT|UPDATE])(.*)/is", "EXPLAIN $1$2", $sql);
        $res = $db->query($sql);
        $res = $res->fetch(PDO::FETCH_ASSOC);
        if($res && is_array($res))
        {   $insert_arr = array();
            if(preg_match("/(optimized away)/isU", $res['Extra']))
            {
                return;
            }
            foreach($res as $k=>$v)
            {
                $v = trim($v);
                if(strtolower($k) == 'key' && !$v)
                {
                    $insert_arr['sql'] = $sql_reg;
                    break;
                }
                if(strtolower($k) == 'possible_keys' && !$v)
                {
                    $insert_arr['sql'] = $sql_reg;
                    break;
                }

                if($v && isset($condition[$k]) && preg_match("/" . $condition[$k] . "/i", $v))
                {
                    $insert_arr['sql'] = $sql_reg;
                    break;
                }
            }
            if(isset($insert_arr['sql']))
            {
                $insert_arr['result'] = '';
                if(isset($res)) $insert_arr['result'] = var_export($res, 1);
                $insert_arr['create_time'] = time();
                $rulebase = new data_rule_rulebase();
                $filter_arr = array('\\', "\0", "\n", "\r", "'", '"', "\x1a", '`', '*', ',' , '(', ')');
                $change_to = array('\\\\', '\\0', '', '\\r', "\\'", '\\"', '\\Z', '', '\*', '\,', '\(', '\)');
                $insert_arr['sql'] = str_replace($filter_arr, $change_to, $insert_arr['sql']);
                $insert_arr['result'] = str_replace($filter_arr, $change_to, $insert_arr['result']);
                $sql = "INSERT INTO " . $table . "(`sql`,`result`,`create_time`) VALUES('"
                        . $insert_arr['sql'] . "', '" . $insert_arr['result']
                        . "'," . $insert_arr['create_time'].")";
                @$db->exec($sql);
            }
        }
    }
}
